﻿PRINT N'ALTER FUNCTION RepRapsZurnalObrashGetFioKlient'

go

ALTER FUNCTION RepRapsZurnalObrashGetFioKlient
(
	@D_KONS		DATETIME, 
	@K_RAB		int
)
RETURNS varchar(1024) 
-- Функция вспомогательная для печати консультаций на журнале обращения
-- Возращает строку типа "номер на журнале обращений (Фамилия И. О. клиента)" для конкретного работника
begin
	
	DECLARE @N_ONE_STROKA varchar(100)
	DECLARE @RetVal varchar(1024)
	DECLARE @V_KONS DateTime
	Set @RetVal = ''
		
	DECLARE ZhurnalObraschCursor cursor LOCAL READ_ONLY FOR 
		SELECT isnull(cast(C_NPP as varchar),'') + ' (' + dbo.CompressFio2(N_FA, N_IM, N_OT) + ')' as N_ONE_STROKA, V_KONS
		FROM b_zhurnal_obrasch 
		WHERE F_PROVEDENA = 1 and K_RAB = @K_RAB AND D_KONS = @D_KONS
		ORDER BY V_KONS
	
	OPEN ZhurnalObraschCursor
	FETCH NEXT FROM ZhurnalObraschCursor into @N_ONE_STROKA, @V_KONS
	

	declare @TimeKonsCur datetime
	
	-- Цикл по данным
	while @@FETCH_STATUS = 0
	BEGIN
		if @RetVal != ''
			Set @RetVal = @RetVal + char(10)
		
		if @TimeKonsCur != @V_KONS or @TimeKonsCur is null
		begin
			Set @RetVal = @RetVal + dbo.GetTime(@V_KONS) + char(10)
			Set @TimeKonsCur = @V_KONS
		end
		
		Set @RetVal = @RetVal + @N_ONE_STROKA
			
		FETCH NEXT FROM ZhurnalObraschCursor into @N_ONE_STROKA, @V_KONS
	END
	
	CLOSE ZhurnalObraschCursor
	DEALLOCATE ZhurnalObraschCursor
	return @RetVal
end


go 
PRINT N'DROP TRIGGER [dbo].[TrigerInsertKpo]'
go
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TrigerInsertKpo]'))
DROP TRIGGER [dbo].[TrigerInsertKpo]

go 
PRINT N'DROP TRIGGER [dbo].[TrigerInsertZhurnalObrasch]'
go
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TrigerInsertZhurnalObrasch]'))
DROP TRIGGER [dbo].[TrigerInsertZhurnalObrasch]




go
PRINT N'insert into p301.dbo.b_zhurnal_obrasch'
go

SET IDENTITY_INSERT p301.dbo.b_zhurnal_obrasch on
go
insert into p301.dbo.b_zhurnal_obrasch (K_ZHURNAL_OBRASCH,C_NPP, D_OBRASCH, V_OBRASCH, K_RAB, D_KONS, V_KONS, F_PROVEDENA, K_PRIZN, K_KPO, K_OBRASCH, K_RAB_ZAPOL, N_FA, N_IM, N_OT)
select back.K_ZHURNAL_OBRASCH,back.C_NPP, back.D_OBRASCH, back.V_OBRASCH, back.K_RAB, back.D_KONS, back.V_KONS, back.F_PROVEDENA, back.K_PRIZN, back.K_KPO, back.K_OBRASCH, back.K_RAB_ZAPOL, back.N_FA, back.N_IM, back.N_OT
from p301ix.dbo.b_zhurnal_obrasch as back left join p301.dbo.b_zhurnal_obrasch as cur
	on back.K_ZHURNAL_OBRASCH = cur.K_ZHURNAL_OBRASCH
where YEAR(back.D_OBRASCH) < 2008 and cur.K_ZHURNAL_OBRASCH is null
go
SET IDENTITY_INSERT p301.dbo.b_zhurnal_obrasch off